package com.spring.demo.core.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.spring.demo.core.dao.IPingDao;
import com.spring.demo.core.entity.Ping;

@Repository
public class PingDao implements IPingDao {

	private JdbcTemplate jdbcTemplate;
	private static final String INSERT_PING_SQL = "insert into ping(age, name, create_time, update_time) values(?, ?, ?, ?)";
	private static final String UPDATE_PING_SQL = "update ping set age=?, name=?, update_time=? where id=?";
	private static final String SELECT_ALL_PING_SQL = "select id, age, name, create_time, update_time from ping";
	private static final String SELECT_PING_SQL = "select id, age, name, create_time, update_time from ping where id=?";
	private static final String DELETE_PING_SQL = "delete from ping where id=?";

	@Autowired
	public void dataSource(
			@Qualifier("embeddedDataSource") DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public Ping saveOrUpdatePing(final Ping ping) {
		Long id = ping.getId();
		if (id == null) {
			KeyHolder keyHolder = new GeneratedKeyHolder();
			PreparedStatementCreator psc = new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					PreparedStatement ps = con
							.prepareStatement(INSERT_PING_SQL);
					ps.setInt(1, ping.getAge());
					ps.setString(2, ping.getName());
					ps.setTimestamp(3, new Timestamp(ping.getCreateTime()
							.getTime()));
					ps.setTimestamp(4, new Timestamp(ping.getUpdateTime()
							.getTime()));
					return ps;
				}
			};
			jdbcTemplate.update(psc, keyHolder);
			ping.setId(keyHolder.getKey().longValue());
		} else {
			PreparedStatementCreator psc = new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					PreparedStatement ps = con
							.prepareStatement(UPDATE_PING_SQL);
					ps.setInt(1, ping.getAge());
					ps.setString(2, ping.getName());
					ps.setTimestamp(3, new Timestamp(ping.getUpdateTime()
							.getTime()));
					ps.setLong(4, ping.getId());
					return ps;
				}
			};
			jdbcTemplate.update(psc);
		}
		return ping;
	}

	@Override
	public Ping findPingById(Long id) {
		return jdbcTemplate.queryForObject(SELECT_PING_SQL,
				new Object[] { id }, new Ping());
	}

	@Override
	public List<Ping> getPings() {
		List<Map<String, Object>> list = jdbcTemplate
				.queryForList(SELECT_ALL_PING_SQL);
		List<Ping> pings = new ArrayList<Ping>();
		for (Map<String, Object> map : list) {
			Ping ping = new Ping();
			ping.setId((Long) map.get("id"));
			ping.setAge((Integer) map.get("age"));
			ping.setName((String) map.get("name"));
			ping.setCreateTime((Date) map.get("create_time"));
			ping.setUpdateTime((Date) map.get("update_time"));

			pings.add(ping);
		}

		return pings;
	}

	@Override
	public boolean deletePingById(Long id) {
		return jdbcTemplate.update(DELETE_PING_SQL, id) == 1;
	}

}
